<!DOCTYPE html>



  


<html class="theme-next muse use-motion" lang="zh-Hans">
<head>
  <meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>
<meta name="theme-color" content="#222">









<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />















  
  
  <link href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css" />




  
  
  
  

  
    
    
  

  

  

  

  

  
    
    
    <link href="//fonts.googleapis.com/css?family=Lato:300,300italic,400,400italic,700,700italic&subset=latin,latin-ext" rel="stylesheet" type="text/css">
  






<link href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css" />

<link href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/css/main.css?v=5.1.2" rel="stylesheet" type="text/css" />


  <meta name="keywords" content="数据库," />








  <link rel="shortcut icon" type="image/x-icon" href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/favicon.ico?v=5.1.2" />






<meta name="description" content="mysql数据库配置登录 mysql -uroot -p密码 mysql -hip -uroot -p 密码 mysql --host&#x3D;ip --user&#x3D;root --password&#x3D;密码  目录结构 配置文件 my.ini  数据data目录:MySQL Server 5.5&#x2F;Data&#x2F;   SQLDDL(数据库操作和表的操作)库创建(Create) 创建数据库 create databas">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql">
<meta property="og:url" content="https://gitee.com/bhblinux/blog.git/2020/07/22/mysql/index.html">
<meta property="og:site_name" content="Bhblinux个人博客">
<meta property="og:description" content="mysql数据库配置登录 mysql -uroot -p密码 mysql -hip -uroot -p 密码 mysql --host&#x3D;ip --user&#x3D;root --password&#x3D;密码  目录结构 配置文件 my.ini  数据data目录:MySQL Server 5.5&#x2F;Data&#x2F;   SQLDDL(数据库操作和表的操作)库创建(Create) 创建数据库 create databas">
<meta property="article:published_time" content="2020-07-22T05:23:43.000Z">
<meta property="article:modified_time" content="2020-07-22T16:50:56.341Z">
<meta property="article:author" content="Bhblinux">
<meta property="article:tag" content="数据库">
<meta name="twitter:card" content="summary">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/心底的希望/blog/',
    scheme: 'Muse',
    sidebar: {"position":"left","display":"post","offset":12,"offset_float":12,"b2t":false,"scrollpercent":false,"onmobile":false},
    fancybox: true,
    tabs: true,
    motion: true,
    duoshuo: {
      userId: '0',
      author: '博主'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="https://gitee.com/bhblinux/blog.git/2020/07/22/mysql/"/>





  <title>mysql | Bhblinux个人博客</title>
  














<meta name="generator" content="Hexo 4.2.1"></head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail ">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/心底的希望/blog/"  class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">Bhblinux个人博客</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
      
        <p class="site-subtitle"></p>
      
  </div>

  <div class="site-nav-toggle">
    <button>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br />
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/archives/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br />
            
            归档
          </a>
        </li>
      
        
        <li class="menu-item menu-item-categories">
          <a href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/categories" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-th"></i> <br />
            
            分类
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/tags/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br />
            
            标签
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/about" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-user"></i> <br />
            
            关于
          </a>
        </li>
      
        
        <li class="menu-item menu-item-search">
          <a href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/search" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-question-circle"></i> <br />
            
            搜索
          </a>
        </li>
      
        
        <li class="menu-item menu-item-commonweal">
          <a href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/404.html" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-heartbeat"></i> <br />
            
            公益404
          </a>
        </li>
      

      
    </ul>
  

  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://gitee.com/bhblinux/blog.git/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/2020/07/22/mysql/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="Bhblinux">
      <meta itemprop="description" content="">
      <meta itemprop="image" content="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/images/avatar.gif">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Bhblinux个人博客">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">mysql</h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2020-07-22T13:23:43+08:00">
                2020-07-22
              </time>
            

            

            
          </span>

          
            <span class="post-category" >
            
              <span class="post-meta-divider">|</span>
            
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              
                <span class="post-meta-item-text">分类于</span>
              
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/" itemprop="url" rel="index">
                    <span itemprop="name">数据库</span>
                  </a>
                </span>

                
                
              
            </span>
          

          
            
          

          
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <h1 id="mysql"><a href="#mysql" class="headerlink" title="mysql"></a>mysql</h1><h3 id="数据库配置"><a href="#数据库配置" class="headerlink" title="数据库配置"></a>数据库配置</h3><h4 id="登录"><a href="#登录" class="headerlink" title="登录"></a>登录</h4><ol>
<li><code>mysql -uroot -p密码</code></li>
<li><code>mysql -hip -uroot -p 密码</code></li>
<li><code>mysql --host=ip --user=root --password=密码</code></li>
</ol>
<h4 id="目录结构"><a href="#目录结构" class="headerlink" title="目录结构"></a>目录结构</h4><ol>
<li><p>配置文件 my.ini</p>
</li>
<li><p>数据data目录:<code>MySQL Server 5.5/Data/</code></p>
</li>
</ol>
<h3 id="SQL"><a href="#SQL" class="headerlink" title="SQL"></a>SQL</h3><h4 id="DDL-数据库操作和表的操作"><a href="#DDL-数据库操作和表的操作" class="headerlink" title="DDL(数据库操作和表的操作)"></a>DDL(数据库操作和表的操作)</h4><h5 id="库创建-Create"><a href="#库创建-Create" class="headerlink" title="库创建(Create)"></a>库创建(Create)</h5><ol>
<li><p>创建数据库</p>
<p><code>create database 数据库名;</code></p>
</li>
<li><p>创建数据库,判断不存在,再创建</p>
<p><code>create database if not exists 数据库名;</code></p>
</li>
<li><p>创建数据库,并指定字符集</p>
<p><code>crate database 数据库名称 character set 字符集名;</code></p>
</li>
<li><p>练习:创建db4数据库,判断是否存在,并指定字符集为gbk</p>
<p><code>crate database if not exists db4 character set gbk;</code></p>
</li>
</ol>
<h5 id="库查询-Retrieve"><a href="#库查询-Retrieve" class="headerlink" title="库查询(Retrieve)"></a>库查询(Retrieve)</h5><ol>
<li><p>查询所有数据库名称</p>
<p><code>show databases;</code></p>
<ol start="2">
<li>查询某个数据库的字符集,查询某个数据库的创建语句</li>
</ol>
<p><code>show create database db4;</code></p>
</li>
</ol>
<h5 id="库修改-update"><a href="#库修改-update" class="headerlink" title="库修改(update)"></a>库修改(update)</h5><ol>
<li><p>修改数据库的字符集</p>
<p><code>ALTER DATABASE db4 CHARACTER  SET utf8;</code></p>
</li>
</ol>
<h5 id="库删除-delete"><a href="#库删除-delete" class="headerlink" title="库删除(delete)"></a>库删除(delete)</h5><ol>
<li><p>删除数据库</p>
<p><code>drop database 数据库名;</code></p>
<ol start="2">
<li>判断数据库存在,存在在删除</li>
</ol>
<p><code>drop database if exists 数据库名;</code></p>
</li>
</ol>
<h5 id="使用数据库"><a href="#使用数据库" class="headerlink" title="使用数据库"></a>使用数据库</h5><ol>
<li><p>查询当前正在使用的数据库名称</p>
<p><code>select database();</code></p>
<ol start="2">
<li>使用数据库</li>
</ol>
<p><code>use 数据库名称;</code></p>
</li>
</ol>
<h4 id=""><a href="#" class="headerlink" title=""></a></h4><h5 id="表创建"><a href="#表创建" class="headerlink" title="表创建"></a>表创建</h5><ol>
<li><p>语法</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">create table 表名(</span><br><span class="line">	列名,数据类型,</span><br><span class="line">	...</span><br><span class="line">	列名n,数据类型</span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<ol start="2">
<li><p>数据类型</p>
<ol>
<li>int 整数型</li>
<li>double 小数型</li>
<li>data 日期,只包含yyyy-MM-dd</li>
<li>datatime yyyy-MM-dd HH:mm:ss</li>
<li>timestamp 时间戳类型如果不给该字段赋值或为null,默认使用系统当前时间</li>
<li>varchar字符串</li>
</ol>
</li>
<li><p>创建表</p>
</li>
</ol>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE student(</span><br><span class="line">	id INT,</span><br><span class="line">	sname VARCHAR(20),</span><br><span class="line">	age INT,</span><br><span class="line">	score DOUBLE(4,1),</span><br><span class="line">	birthday DATE,</span><br><span class="line">	insert_time TIMESTAMP</span><br><span class="line">	</span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<ol start="4">
<li>复制表</li>
</ol>
<p><code>create table 表名 like 被复制的表名</code></p>
</li>
</ol>
<h5 id="表查询"><a href="#表查询" class="headerlink" title="表查询"></a>表查询</h5><ol>
<li><p>查询某个数据库中的所有表</p>
<p><code>show tables;</code></p>
<ol start="2">
<li>查询表的结构</li>
</ol>
<p><code>desc 表名</code></p>
</li>
</ol>
<h5 id="表修改"><a href="#表修改" class="headerlink" title="表修改"></a>表修改</h5><ol>
<li><p>修改表名</p>
<p><code>alter table 表名 rename to 新表名;</code></p>
</li>
<li><p>修改表的字符集</p>
<p><code>alter table 表名 character set 字符集;</code></p>
</li>
<li><p>添加一列</p>
<p><code>alter table 表名 add 列名 数据类型;</code></p>
</li>
<li><p>修改列名称 类型</p>
<pre><code>1. `alter table 表名 change 列名 新列名 新数据类型;`
2. `alter table 表名 modify 列名 新数据类型;`</code></pre></li>
<li><p>删除列</p>
<p><code>alter table 表名 drop 列名;</code></p>
</li>
</ol>
<h5 id="表删除"><a href="#表删除" class="headerlink" title="表删除"></a>表删除</h5><pre><code>1. `drop table 表名;`
 2. `drop table if exists 表名;</code></pre><h4 id="DML-表数据的增删改查"><a href="#DML-表数据的增删改查" class="headerlink" title="DML(表数据的增删改查)"></a>DML(表数据的增删改查)</h4><h5 id="添加数据"><a href="#添加数据" class="headerlink" title="添加数据"></a>添加数据</h5><pre><code>1. 语法`insert into 表名(列名1,列名2...列名n) values (值1,值2,...值n);`</code></pre><h5 id="删除数据"><a href="#删除数据" class="headerlink" title="删除数据"></a>删除数据</h5><pre><code>1. 语法 `delete from 表名 [where 条件];`
 2. 删除所有记录
      1. `delete from 表名;` 不推荐
      2. `truncate table 表名;`</code></pre><h5 id="修改数据"><a href="#修改数据" class="headerlink" title="修改数据"></a>修改数据</h5><pre><code>1. `update 表名 set 列名1= 值1,列2=值2 [where 条件];`
 2. 如果不加条件,修改全部数据</code></pre><h4 id="DQL-表的查询"><a href="#DQL-表的查询" class="headerlink" title="DQL(表的查询)"></a>DQL(表的查询)</h4><ol>
<li><p>语法<code>select * from 表名</code></p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">select </span><br><span class="line">	字段列表</span><br><span class="line">from</span><br><span class="line">	表名列表</span><br><span class="line">grooup by</span><br><span class="line">	分组字段</span><br><span class="line">having</span><br><span class="line">	分组之后的条件</span><br><span class="line">order by</span><br><span class="line">	排序</span><br><span class="line">limit</span><br><span class="line">	分页限定</span><br></pre></td></tr></table></figure>
</li>
<li><p>去重复</p>
<p><code>SELECT DISTINCT(age) FROM stu ;</code></p>
<p><code>select distinct address from student;</code></p>
</li>
<li><p>计算列</p>
<p>ifnull(表达式1,表达式2) : null参与计算,结果都为null</p>
<p><code>select ename,  salary+ifnull(bonus,0) from emp;</code> 如果表达式1位null,则替换后面的值</p>
</li>
<li><p>起别名</p>
<p><code>as</code></p>
</li>
</ol>
<ul>
<li><p>添加查询</p>
<ol>
<li><p>where 子语句后跟条件</p>
</li>
<li><p>运算符</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">&gt;,&lt;,&lt;&#x3D;,&gt;&#x3D; &#x3D;,&lt;&gt;不等</span><br><span class="line">between...and</span><br><span class="line">in(集合)</span><br><span class="line">like 模糊查询</span><br><span class="line">	占位符:</span><br><span class="line">		_ 单个任意字符</span><br><span class="line">		%:多个任意字符</span><br><span class="line">is null 是否为空</span><br><span class="line">is not null 是否不为空</span><br><span class="line">and 或 &amp;&amp;</span><br><span class="line">or 或 ||</span><br><span class="line">not 或 !</span><br></pre></td></tr></table></figure>
</li>
</ol>
</li>
<li><p>多表查询</p>
<ol>
<li><p>内连接</p>
<ul>
<li><p>隐士内连接:</p>
</li>
<li><p>显示内连接</p>
<p><code>select 字段列表 from 表名1 [inner] join 表名2 on 条件</code></p>
</li>
</ul>
</li>
<li><p>外连接</p>
<ol>
<li><p>左外链接</p>
<p><code>select 字段列表 from 表1 left [outer] join 表2 on 条件；</code></p>
</li>
<li><p>右外连接</p>
<p><code>select 字段列表 from 表1 right [outer] join 表2 on 条件；</code></p>
</li>
</ol>
</li>
<li><p>子查询</p>
<ol>
<li><p>单行单列</p>
<p><code>SELECT MAX(salary) FROM emp</code></p>
<p><code>SELECT * FROM emp WHERE salary=(SELECT MAX(salary) FROM emp);</code></p>
</li>
<li><p>多行单列</p>
<ol>
<li><p>子查询可以作为条件，使用运算符in来判断</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line">SELECT id FROM dept WHERE NAME&#x3D;&quot;财务部&quot;;</span><br><span class="line">SELECT id FROM dept WHERE NAME&#x3D;&quot;市场部&quot;;</span><br><span class="line"></span><br><span class="line">SELECT</span><br><span class="line">*</span><br><span class="line">FROM</span><br><span class="line">	emp t1</span><br><span class="line">WHERE</span><br><span class="line">	t1.dept_id IN (SELECT id FROM dept WHERE NAME&#x3D;&quot;财务部&quot; OR NAME&#x3D;&quot;市场部 &quot;);</span><br></pre></td></tr></table></figure>



</li>
</ol>
</li>
</ol>
</li>
</ol>
</li>
</ul>
<pre><code>2. 多行多列

   1. 子查询可以为一张虚拟表参与查询

      <figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">查询员工入职日期是2011-11-11日之后的员工信息和部门信息</span><br><span class="line">SELECT </span><br><span class="line">*</span><br><span class="line">FROM</span><br><span class="line">	(SELECT * FROM emp WHERE join_date &gt;&#x3D; &#39;2011-11-11&#39;) t1,dept t2</span><br><span class="line">WHERE</span><br><span class="line">	t1.dept_id&#x3D;t2.id;</span><br></pre></td></tr></table></figure></code></pre><ul>
<li><p>分页查询</p>
<ol>
<li>语法 limit 开始索引,每页查询的条数</li>
<li>公式:开始索引 = (当前页码-1) * 每页显示的条数</li>
</ol>
</li>
<li><p>约束</p>
<ol>
<li>主键约束 <code>primary key</code></li>
<li>非空约束 <code>not null</code></li>
<li>唯一约束 <code>unique</code></li>
<li>外键约束 <code>foreign key</code></li>
</ol>
</li>
<li><p>非空约束</p>
<ol>
<li><p>创建表的时候添加约束</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">create table stu(</span><br><span class="line">	id int,</span><br><span class="line">	name varchar(20) not null</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
</li>
<li><p>表创建完后,添加非空约束</p>
<p><code>alter table stu modify name varchar(20) not null;</code></p>
</li>
<li><p>删除非空约束</p>
<p><code>alter table stu modify name varchar(20);</code></p>
</li>
</ol>
</li>
<li><p>唯一约束</p>
<ol>
<li><p>在创建表后,添加唯一约束</p>
<p><code>alter table stu modify  name varchar(20) unique;</code></p>
</li>
<li><p>删除唯一约束</p>
<p><code>alter table stu drop index name;</code></p>
</li>
</ol>
</li>
<li><p>主键约束</p>
<ol>
<li><p>表创建后,添加主键</p>
<p><code>alter table stu modify id int primary key;</code></p>
</li>
<li><p>删除主键</p>
<p><code>alter table stu drop primary key;</code></p>
</li>
</ol>
</li>
<li><p>自增长(auto_increment)</p>
<ol>
<li><p>添加自增长</p>
<p><code>alter table stu id int auto_increment;</code></p>
</li>
<li><p>删除自增长</p>
<p><code>alter table stu modify id int;</code></p>
</li>
</ol>
</li>
<li><p>外键约束</p>
<ol>
<li><p>创建表的时候,添加外键</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">create table table_name(</span><br><span class="line">	...</span><br><span class="line">	外键</span><br><span class="line">	constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
</li>
<li><p>删除外键</p>
<p><code>alter table 表名 drop foreign key 外键名称</code></p>
</li>
<li><p>创建表后,新增外键</p>
<p><code>alter table stu add constraint 外键名称 foreign key(外键字段名称) references 主表(主表列名称);</code></p>
</li>
</ol>
</li>
<li><p>级联操作</p>
<ol>
<li><p>添加级联</p>
<p><code>alter table 表名 add constraint 外键名称 foreign key (外键字段名称)  references 主表名称(主表列) on update cascade on delete cascade;</code></p>
</li>
<li><p>级联更新</p>
<p><code>on update cascade</code></p>
</li>
<li><p>级联删除</p>
<p><code>on delete cascade</code></p>
</li>
</ol>
</li>
<li><p>事务</p>
<ol>
<li><p>操作</p>
<ol>
<li>开启事务 <code>start transaction;</code></li>
<li>回滚 <code>rollback</code></li>
<li>提交 <code>commit;</code></li>
</ol>
</li>
<li><p>修改事务的默认提交方式</p>
<ol>
<li>查看默认提交方式 <code>select @@autocommit;</code>1 自动提交 0 手动提交</li>
<li>设置默认提交方式 <code>set @@autocommit =0;</code></li>
</ol>
</li>
<li><p>事务四大特征</p>
<ol>
<li>原子性 要么同时成功,要么同时失败</li>
<li>持久性 当事务提交或回滚,数据库会持久化的保存数据</li>
<li>隔离性 多个事务相互独立</li>
<li>一致性 事务操作前后,数据总量不变</li>
</ol>
</li>
<li><p>事务隔离级别</p>
<ol>
<li>脏读 一个事务读取到另一个事务中没有提交的数据</li>
<li>不可重复读 同一个事务中,两次读取的数据不一样</li>
<li>幻读 一个事务操作表中所有记录,另一个事务添加一个数据,则第一个事务查不到自己修改的数据</li>
</ol>
</li>
<li><p>隔离级别</p>
<ol>
<li>read uncommitted: 读未提交 脏读,不可重复读,幻读</li>
<li>read committed 读已提交 产生不可重复度</li>
<li>repeatable read  可重复度 (mysql默认)  产生幻读</li>
<li>serializable 串行化 可以解决所有问题</li>
</ol>
</li>
<li><p>查询数据库隔离级别</p>
<p><code>SELECT @@tx_isolation;</code></p>
</li>
<li><p>设置隔离级别</p>
<p><code>set global transaction isolation level 级别字符串;</code></p>
</li>
</ol>
</li>
</ul>
<h4 id="DCL"><a href="#DCL" class="headerlink" title="DCL"></a>DCL</h4><h5 id="用户管理"><a href="#用户管理" class="headerlink" title="用户管理"></a>用户管理</h5><ol>
<li><p>添加用户</p>
<p><code>create user &#39;用户名&#39;@&#39;主机名&#39; identified by &#39;密码&#39;;</code></p>
</li>
<li><p>删除用户</p>
<p><code>drop user &#39;用户名&#39;@&#39;主机名&#39;</code></p>
</li>
<li><p>修改用户密码</p>
<p><code>update user set password = password(&#39;新密码&#39;) where user=&#39;用户名&#39;</code></p>
<p><code>update user set password=password(&#39;123456&#39;)  where user=&#39;bhblinux&#39;</code></p>
<p><code>set password for &#39;用户名&#39;@&#39;主机名&#39; = password(&#39;新密码&#39;);</code></p>
<p><code>set password for &#39;root&#39;@&#39;localhost&#39; = password(&#39;123&#39;);</code></p>
<ul>
<li>如果忘记root密码<ol>
<li>停止mysql服务</li>
<li>使用无验证方式启动mysql服务 mysqld –skip-grant-tables</li>
<li>use mysql;</li>
<li>update user set password=password(‘新密码’) where user =  ‘root’;</li>
<li>重启mysql服务</li>
</ol>
</li>
</ul>
</li>
<li><p>查询用户</p>
<ol>
<li><p>切换mysql数据库</p>
<p><code>use msyql;</code></p>
</li>
<li><p>查询user表</p>
<p><code>select * from mysql;</code></p>
</li>
<li><p>通配符% 表示可以在任意主机使用登录数据库</p>
</li>
</ol>
</li>
</ol>
<h5 id="权限管理"><a href="#权限管理" class="headerlink" title="权限管理"></a>权限管理</h5><ol>
<li>查询权限<ol>
<li><code>show grants for &#39;用户名&#39;@&#39;主机名&#39;;</code></li>
</ol>
</li>
<li>授予权限<ol>
<li><code>grant 权限列表 on 数据库名.表名 to &#39;用户名&#39;@&#39;主机名&#39;</code></li>
<li>赋予张三任意权限 <code>GRANT ALL ON *.* TO &#39;zhangsan&#39;@&#39;localhost&#39;;</code></li>
</ol>
</li>
<li>撤销权限<ol>
<li><code>revoke 权限列表 on 数据库名.表名 from &#39;用户名&#39;@&#39;主机名&#39;;</code></li>
<li><code>REVOKE UPDATE ON db3.</code>account<code>FROM &#39;lisi&#39;@&#39;%&#39;;</code></li>
</ol>
</li>
</ol>
<h3 id="数据库备份和还原"><a href="#数据库备份和还原" class="headerlink" title="数据库备份和还原"></a>数据库备份和还原</h3><ol>
<li><p>备份</p>
<p><code>mysqldump -u 用户名 -p密码 数据库名称 &gt; 保存路径</code></p>
</li>
<li><p>还原</p>
<ol>
<li>登录</li>
<li>创建数据库</li>
<li>使用数据库</li>
<li>执行source 文件路径</li>
</ol>
</li>
</ol>

      
    </div>
    
    
    

    

    

    

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/" rel="tag"># 数据库</a>
          
        </div>
      

      
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/2020/07/22/hello-world/" rel="next" title="Hello World">
                <i class="fa fa-chevron-left"></i> Hello World
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/2020/07/23/jdbc/" rel="prev" title="jdbc">
                jdbc <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>



    <div class="post-spread">
      
    </div>
  </div>


          </div>
          


          
  <div class="comments" id="comments">
    
  </div>


        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap" >
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
          <img class="site-author-image" itemprop="image"
               src="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/images/avatar.gif"
               alt="Bhblinux" />
          <p class="site-author-name" itemprop="name">Bhblinux</p>
           
              <p class="site-description motion-element" itemprop="description">个人博客系统</p>
          
        </div>
        <nav class="site-state motion-element">

          
            <div class="site-state-item site-state-posts">
              <a href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/archives/">
                <span class="site-state-item-count">3</span>
                <span class="site-state-item-name">日志</span>
              </a>
            </div>
          

          
            
            
            <div class="site-state-item site-state-categories">
              <a href="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/categories/index.html">
                <span class="site-state-item-count">1</span>
                <span class="site-state-item-name">分类</span>
              </a>
            </div>
          

          
            
            
            <div class="site-state-item site-state-tags">
              
                <span class="site-state-item-count">3</span>
                <span class="site-state-item-name">标签</span>
              
            </div>
          

        </nav>

        

        <div class="links-of-author motion-element">
          
        </div>

        
        

        
        

        


      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#mysql"><span class="nav-number">1.</span> <span class="nav-text">mysql</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#数据库配置"><span class="nav-number">1.0.1.</span> <span class="nav-text">数据库配置</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#登录"><span class="nav-number">1.0.1.1.</span> <span class="nav-text">登录</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#目录结构"><span class="nav-number">1.0.1.2.</span> <span class="nav-text">目录结构</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#SQL"><span class="nav-number">1.0.2.</span> <span class="nav-text">SQL</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#DDL-数据库操作和表的操作"><span class="nav-number">1.0.2.1.</span> <span class="nav-text">DDL(数据库操作和表的操作)</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#库创建-Create"><span class="nav-number">1.0.2.1.1.</span> <span class="nav-text">库创建(Create)</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#库查询-Retrieve"><span class="nav-number">1.0.2.1.2.</span> <span class="nav-text">库查询(Retrieve)</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#库修改-update"><span class="nav-number">1.0.2.1.3.</span> <span class="nav-text">库修改(update)</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#库删除-delete"><span class="nav-number">1.0.2.1.4.</span> <span class="nav-text">库删除(delete)</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#使用数据库"><span class="nav-number">1.0.2.1.5.</span> <span class="nav-text">使用数据库</span></a></li></ol></li><li class="nav-item nav-level-4"><a class="nav-link" href="#"><span class="nav-number">1.0.2.2.</span> <span class="nav-text"></span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#表创建"><span class="nav-number">1.0.2.2.1.</span> <span class="nav-text">表创建</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#表查询"><span class="nav-number">1.0.2.2.2.</span> <span class="nav-text">表查询</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#表修改"><span class="nav-number">1.0.2.2.3.</span> <span class="nav-text">表修改</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#表删除"><span class="nav-number">1.0.2.2.4.</span> <span class="nav-text">表删除</span></a></li></ol></li><li class="nav-item nav-level-4"><a class="nav-link" href="#DML-表数据的增删改查"><span class="nav-number">1.0.2.3.</span> <span class="nav-text">DML(表数据的增删改查)</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#添加数据"><span class="nav-number">1.0.2.3.1.</span> <span class="nav-text">添加数据</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#删除数据"><span class="nav-number">1.0.2.3.2.</span> <span class="nav-text">删除数据</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#修改数据"><span class="nav-number">1.0.2.3.3.</span> <span class="nav-text">修改数据</span></a></li></ol></li><li class="nav-item nav-level-4"><a class="nav-link" href="#DQL-表的查询"><span class="nav-number">1.0.2.4.</span> <span class="nav-text">DQL(表的查询)</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#DCL"><span class="nav-number">1.0.2.5.</span> <span class="nav-text">DCL</span></a><ol class="nav-child"><li class="nav-item nav-level-5"><a class="nav-link" href="#用户管理"><span class="nav-number">1.0.2.5.1.</span> <span class="nav-text">用户管理</span></a></li><li class="nav-item nav-level-5"><a class="nav-link" href="#权限管理"><span class="nav-number">1.0.2.5.2.</span> <span class="nav-text">权限管理</span></a></li></ol></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#数据库备份和还原"><span class="nav-number">1.0.3.</span> <span class="nav-text">数据库备份和还原</span></a></li></ol></li></ol></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright" >
  
  &copy; 
  <span itemprop="copyrightYear">2020</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">Bhblinux</span>
</div>


<div class="powered-by">
  由 <a class="theme-link" href="https://hexo.io" target="_blank" rel="noopener">Hexo</a> 强力驱动
</div>

<div class="theme-info">
  主题 -
  <a class="theme-link" href="https://github.com/iissnan/hexo-theme-next" target="_blank" rel="noopener">
    NexT.Muse
  </a>
</div>


        

        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
      </div>
    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  












  
  <script type="text/javascript" src="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/lib/jquery/index.js?v=2.1.3"></script>

  
  <script type="text/javascript" src="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>

  
  <script type="text/javascript" src="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>

  
  <script type="text/javascript" src="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/lib/velocity/velocity.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>

  
  <script type="text/javascript" src="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>


  


  <script type="text/javascript" src="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/js/src/utils.js?v=5.1.2"></script>

  <script type="text/javascript" src="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/js/src/motion.js?v=5.1.2"></script>



  
  

  
  <script type="text/javascript" src="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/js/src/scrollspy.js?v=5.1.2"></script>
<script type="text/javascript" src="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/js/src/post-details.js?v=5.1.2"></script>



  


  <script type="text/javascript" src="/%E5%BF%83%E5%BA%95%E7%9A%84%E5%B8%8C%E6%9C%9B/blog/js/src/bootstrap.js?v=5.1.2"></script>



  


  




	





  





  






  





  

  

  

  

  

  

</body>
</html>
